# encoding=utf-8
import requests #导入requests模块
import json
import pprint
import xlrd # 读
from openpyxl import Workbook # 写

# 按页拉取数据
def fetch_data_by_page(page_num, phone_no, page_size = 10):
    url="https://portal.evscrm.ford.com.cn/member-admin/usercenter/userinfo-list?pageNo={PageNo}&pageSize={PageSize}&startTime=&endTime=&aId=&nickName=&phoneNum={PhoneNo}&joinChannel=&cutFlag=0&memberFlag=".format(PageNo=page_num, PageSize=page_size, PhoneNo=phone_no)
    hd = {}
    hd["token"] = "eyJhbGciOiJIUzI1NiJ9.eyJqdGkiOiJNRU1CRVJfQ0VOVEVSX0pXVF9JRCIsImlhdCI6MTY0NTY3NDQ0MywiaXNzIjoibWVtYmVyQ2VudGVyIiwic3ViIjoie1wiaWRcIjo0MzgsXCJsb2dpbk5hbWVcIjpcImxsaTE4NWZvcmRcIixcInJlZnJlc2hUb2tlblwiOlwiZXlKaGJHY2lPaUpJVXpJMU5pSXNJblI1Y0NJZ09pQWlTbGRVSWl3aWEybGtJaUE2SUNJNU5HRXlaR1ExWkMwNU1HVm1MVFExTW1VdE9HWmhaaTB3WlRWa05EWXlZVEpoWTJVaWZRLmV5SmxlSEFpT2pFMk5EVTJOemd6TkRNc0ltbGhkQ0k2TVRZME5UWTNORFEwTXl3aWFuUnBJam9pTkRBMVltSmlZall0TnpSbFppMDBNV0k0TFRrMFltVXRaakJsWm1Kak1XRXpPRGd4SWl3aWFYTnpJam9pYUhSMGNITTZMeTloZFhSb0xtVjJjMk55YlM1bWIzSmtMbU52YlM1amJpOWhkWFJvTDNKbFlXeHRjeTl0WVhOMFpYSWlMQ0poZFdRaU9pSm9kSFJ3Y3pvdkwyRjFkR2d1WlhaelkzSnRMbVp2Y21RdVkyOXRMbU51TDJGMWRHZ3ZjbVZoYkcxekwyMWhjM1JsY2lJc0luTjFZaUk2SW1aak5qYzNZekF4TFRFM05XVXRORFpqWVMwNFpqQTNMVGcwTUdabE1UZG1aakEzT0NJc0luUjVjQ0k2SWxKbFpuSmxjMmdpTENKaGVuQWlPaUp0WlcxaVpYSXRZV1J0YVc0aUxDSnpaWE56YVc5dVgzTjBZWFJsSWpvaVkyVXhPREpsTldFdFpHUXdNaTAwWlRJeExXSTNNRGN0T0RobFlUSTVaV0k0WW1Veklpd2ljMk52Y0dVaU9pSndjbTltYVd4bEluMC45VnBIVVoxNnk0c2NXNmt6Y1ZYTmsyQ1pvYnFKRzNqODJhZnBJMDJWd29ZXCIsXCJyb2xlQ29kZVwiOlwiTWVtYmVyX2luZm9cIixcInVzZXJOYW1lXCI6XCJMaW5hbiBMaVwifSIsImV4cCI6MTY0NTc2MDg0M30.KSvlQvhtD_W8Wyqnh91Fz4l9-YxqPwDs4TZZMSpY5NE"
    
    response = requests.get(url=url,headers=hd)
    recv_json_data = response.json()
    pprint.pprint(recv_json_data)

    return

    # 返回本次请求数据列表
    result_list = parse_response_data(recv_json_data)
    llen = len(result_list)
    if llen < page_size:
        # 说明已经是最后一页了
        print(llen)
    else:
        print(llen)

# 根据电话查询信息
def find_data_by_phoneno(phone_no):
    url="https://portal.evscrm.ford.com.cn/member-admin/usercenter/userinfo-list?pageNo=1&pageSize=10&startTime=&endTime=&aId=&nickName=&phoneNum={PhoneNo}&joinChannel=&cutFlag=0&memberFlag=".format(PhoneNo=phone_no)
    hd = {}
    hd["token"] = "eyJhbGciOiJIUzI1NiJ9.eyJqdGkiOiJNRU1CRVJfQ0VOVEVSX0pXVF9JRCIsImlhdCI6MTY0NTc1ODg5NiwiaXNzIjoibWVtYmVyQ2VudGVyIiwic3ViIjoie1wiaWRcIjo0MzgsXCJsb2dpbk5hbWVcIjpcImxsaTE4NWZvcmRcIixcInJlZnJlc2hUb2tlblwiOlwiZXlKaGJHY2lPaUpJVXpJMU5pSXNJblI1Y0NJZ09pQWlTbGRVSWl3aWEybGtJaUE2SUNJNU5HRXlaR1ExWkMwNU1HVm1MVFExTW1VdE9HWmhaaTB3WlRWa05EWXlZVEpoWTJVaWZRLmV5SmxlSEFpT2pFMk5EVTNOakkzT1RZc0ltbGhkQ0k2TVRZME5UYzFPRGc1Tml3aWFuUnBJam9pWkRRNU9ERTBZekV0T0RZMk1TMDBZelF5TFdJeE1HRXRPRFE0WkRJeVpXRTNZemxrSWl3aWFYTnpJam9pYUhSMGNITTZMeTloZFhSb0xtVjJjMk55YlM1bWIzSmtMbU52YlM1amJpOWhkWFJvTDNKbFlXeHRjeTl0WVhOMFpYSWlMQ0poZFdRaU9pSm9kSFJ3Y3pvdkwyRjFkR2d1WlhaelkzSnRMbVp2Y21RdVkyOXRMbU51TDJGMWRHZ3ZjbVZoYkcxekwyMWhjM1JsY2lJc0luTjFZaUk2SW1aak5qYzNZekF4TFRFM05XVXRORFpqWVMwNFpqQTNMVGcwTUdabE1UZG1aakEzT0NJc0luUjVjQ0k2SWxKbFpuSmxjMmdpTENKaGVuQWlPaUp0WlcxaVpYSXRZV1J0YVc0aUxDSnpaWE56YVc5dVgzTjBZWFJsSWpvaU5UWmtPV05tWkRRdFkyVTRaaTAwTVdGakxUZ3hOV0V0WmpVd05tTmlObUUxTjJOaUlpd2ljMk52Y0dVaU9pSndjbTltYVd4bEluMC5qLVJPa1dFZjc4WUhlZVgybk9VMlQ5QzdKLWxkUk9fY3hnSnlURUkyM2dFXCIsXCJyb2xlQ29kZVwiOlwiTWVtYmVyX2luZm9cIixcInVzZXJOYW1lXCI6XCJMaW5hbiBMaVwifSIsImV4cCI6MTY0NTg0NTI5Nn0.gLrhDyJumsZzGnDAIMTZKJtwGcbo3Bf9EFr8pGwGFhg"

    response = requests.get(url=url,headers=hd)
    recv_json_data = response.json()
    # 返回本次请求数据列表
    result = {}
    result_list = parse_response_data(recv_json_data)
    llen = len(result_list)
    if 0 != llen:
        result = result_list[0]
    return result



# 解析响应数据
def parse_response_data(recv_json_data):
    dd = recv_json_data
    return dd["result"]


# 从excel读手机号
def read_phone_list_from_excel():
    excel_path = "/Users/gongwantao/Documents/Program/wgy/wgy_lab/color_lab/lab_num_config.xls"
    eb = xlrd.open_workbook(excel_path)
    s1 = eb.sheet_by_name("Sheet1")
    num_rows = s1.nrows
    print(num_rows)
    phone_list = []
    for index in range(1, num_rows):
        pn = int(s1.cell_value(index, 10))
        phone_list.append(int(pn))
    return phone_list

# 
def write_data_to_excel(all_line):
    wb = Workbook()
    ws = wb.active
    for li in all_line:
        ws.append(li)
    wb.save("./saxa.xlsx")

# 用户信息数据结构
class user_item:
    def __init__(self):
        self.phone_no = 0
        self.create_time = ""
        self.integral = 0
    def debug(self):
        return "phone_no: {0}, create_time = {1}, integral = {2}".format(self.phone_no, self.create_time, self.integral)
    def __str__(self) -> str:
        return self.debug()
    def __repr__(self) -> str:
        return self.debug()
    # 返回title
    def title():
        l = []
        phone = "手机号"
        ct    = "入会时间"
        score = "积分"
        l.append(phone)
        l.append(ct)
        l.append(score)
        return l
    
    # 转换成list
    def convert_to_list(self):
        result = []
        result.append(str(self.phone_no))
        result.append(str(self.create_time))
        result.append(str(self.integral))
        return result

# 根据电话号码列表查询用户的信息 返回字典
def query_user_info_by_phone_list(phone_list):
    user_info_map = {} # 用户信息映射 {"13627291809": user_item()}
    # 遍历手机号查每个手机号的信息
    for item in phone_list:
        ui = user_item()
        ui.phone_no = item
        result = find_data_by_phoneno(item)
        if not isinstance(result, dict):
            print("手机号: " + str(item) + " 结果错误")
            continue
        if {} == result:
            print("手机号: " + str(item) + " 没有匹配的信息")
            continue
        if "createTime" in result.keys():
            create_time = result["createTime"]
            integral    = result["integral"]
            ui.create_time = create_time
            ui.integral    = integral
            if item in result.keys():
                print("出错 跳过")
                continue
            else:
                user_info_map[item] = ui
            print("手机号: " + str(item) + " 入会时间: " + str(create_time) + " 积分: " + str(integral))
        else:
            print("手机号: " + str(item) + " 查询信息失败")
    return user_info_map

# 信息写入excel
def write_user_info_into_excel(user_info_map):
    excel_path = "/Users/gwang83/Documents/2.25/SEP相关原始数据梳理统计.xls"
    eb = xlrd.open_workbook(excel_path)
    s1 = eb.sheet_by_name("Sheet1")
    num_rows = s1.nrows
    if num_rows < 1:
        print("table is illegel")
        return

    all_line = []   # 用户存放表的所有行
    # 先写表头
    lv = s1.row_values(0)
    lv.extend(user_item.title())
    all_line.append(lv)
    # 写表值
    for index in range(1, num_rows):
        lv = s1.row_values(index) # 获取该行所属数据
        phone_no = int(lv[10])
        ui = user_item()
        if phone_no in user_info_map.keys():
            ui = user_info_map[phone_no]
        lv.extend(ui.convert_to_list())
        all_line.append(lv)
    write_data_to_excel(all_line)


if __name__=="__main__":
    # write_data_to_excel()

    # 1.从excel里面读手机号
    phone_list = read_phone_list_from_excel()
  
    # phone_list = [15510954835, 15510954836, 15510954837, 13627291809]
    
    # 2.查询各个手机号对应的信息
    user_info_map = {} # 用户信息映射 {"13627291809": user_item()}
    user_info_map = query_user_info_by_phone_list(phone_list)

    # 3.写入excel
    write_user_info_into_excel(user_info_map)

